import pymysql
import pandas as pd


def load_insurance_data():
    """加载保单明细数据"""
    dou_files = [
        (r'G:\工作\2025年订单\9月\财务匹配\保单明细-1.xlsx', '抖音1'),
        (r'G:\工作\2025年订单\9月\财务匹配\保单明细-2.xlsx', '抖音2'),
        (r'G:\工作\2025年订单\9月\财务匹配\保单明细-3.xlsx', '抖音3'),
        (r'G:\工作\2025年订单\9月\财务匹配\保单明细-4.xlsx', '抖音4')
    ]

    dou_dfs = []
    for file_path, platform in dou_files:
        df = pd.read_excel(file_path, dtype={'订单编号': str})
        df['平台'] = platform
        dou_dfs.append(df)

    dou = pd.concat(dou_dfs)
    return dou[dou['保费状态'] == '已扣减']


def get_channel_data():
    """从数据库获取渠道数据"""
    db = pymysql.connect(host="localhost", user="root", password="hui123456", db='dbtest')
    cursor = db.cursor()

    try:
        sql = """
            SELECT 
                orderid2, zqudao 
            FROM orderid_qd 
            WHERE MONTH(day) = 9
        """
        cursor.execute(sql)
        result = cursor.fetchall()
        qd = pd.DataFrame(list(result))
        qd.columns = ['订单编号', '最终渠道']
        return qd
    finally:
        cursor.close()
        db.close()


def save_by_platform(res):
    """按平台保存数据"""
    for i in range(1, 5):
        res_dou = res[res['平台'] == f'抖音{i}']
        res_dou.to_excel(
            rf'G:\工作\2025年订单\9月\财务匹配\渠道区分\保单明细{i}店.xlsx',
            index=False
        )


def main():
    """主函数"""
    # 读取保单明细数据
    dou = load_insurance_data()

    # 获取渠道数据
    qd = get_channel_data()

    # 数据合并
    res = pd.merge(dou, qd, on='订单编号', how='left')

    # 按平台分组保存
    save_by_platform(res)

    # 汇总统计
    summary = res.groupby([res['最终渠道']], as_index=False).agg(保费总=('支付保费', 'sum'))
    summary.to_excel(r'G:\工作\2025年订单\9月\财务匹配\渠道区分\保单明细.xlsx', index=False)


if __name__ == "__main__":
    main()
